/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.altius.dao.impl;

import cc.altius.dao.UserDao;
import cc.altius.domain.Password;
import cc.altius.domain.Role;
import cc.altius.domain.User;
import cc.altius.domain.mapper.RoleRowMapper;
import cc.altius.domain.mapper.UserRowMapper;
import cc.altius.utils.DateUtils;
import cc.altius.utils.LogUtils;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.security.authentication.encoding.Md5PasswordEncoder;
import org.springframework.security.authentication.encoding.PasswordEncoder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author Akil Mahimwala
 */
@Repository("userDao")
public class UserDaoImpl implements UserDao {

    private JdbcTemplate jdbcTemplate;
    private DataSource dataSource;
    private SessionFactory sessionFactory;
    private HibernateTemplate hibernateTemplate;
    private Session session;
    private Query query;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
        this.hibernateTemplate = new HibernateTemplate(sessionFactory);
    }

    @Override
    public List<User> getUserList() {
        return this.jdbcTemplate.query("SELECT user.*, user_role.ROLE_ID, role.ROLE_NAME FROM user LEFT JOIN user_role ON user.USER_ID=user_role.USER_ID LEFT JOIN role ON user_role.ROLE_ID=role.ROLE_ID", new UserRowMapper());
    }

    @Override
    public List<Role> getRoleList() {
        return this.jdbcTemplate.query("SELECT * FROM role", new RoleRowMapper());
    }

    @Override
    @Transactional
    public int addUser(User user) {
        SimpleJdbcInsert userInsert = new SimpleJdbcInsert(this.dataSource).withTableName("user").usingGeneratedKeyColumns("USER_ID");
        Map<String, Object> params = new HashMap<String, Object>();
        PasswordEncoder encoder = new Md5PasswordEncoder();
        String hashPass = encoder.encodePassword(user.getPassword(), null);
        params.put("USERNAME", user.getUsername());
        params.put("PASSWORD", hashPass);
        params.put("ACTIVE", user.isActive());
        params.put("EXPIRED", false);
        params.put("EXPIRES_ON", DateUtils.getOffsetFromCurrentDateObject(DateUtils.IST, 60));
        params.put("FAILED_ATTEMPTS", 0);
        int userId = userInsert.executeAndReturnKey(params).intValue();
        params.clear();
        //String sqlString = "UPDATE user SET PASSWORD=md5(?) WHERE user.USER_ID=?";
        //this.jdbcTemplate.update(sqlString, user.getPassword(), userId);
        //params.put("USER_ID", userId);
        //params.put("ROLE_ID", user.getRole().getRoleId());
        String sqlString = "INSERT INTO user_role (USER_ID, ROLE_ID) VALUES(?, ?)";
        LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
        this.jdbcTemplate.update(sqlString, userId, user.getRole().getRoleId());
        return userId;
    }

    @Override
    @Transactional
    public void updateUser(User user) {
        String sqlString;
        Object params[];
        if (user.getPassword().isEmpty()) {
            sqlString = "UPDATE user SET USERNAME=?, ACTIVE=? WHERE USER_ID=?";
            params = new Object[]{user.getUsername(), user.isActive(), user.getUserId()};
            LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
            this.jdbcTemplate.update(sqlString, params);
        } else {
            sqlString = "UPDATE user SET USERNAME=?, PASSWORD=md5(?), ACTIVE=? WHERE USER_ID=?";
            params = new Object[]{user.getUsername(), user.getPassword(), user.isActive(), user.getUserId()};
            LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
            this.jdbcTemplate.update(sqlString, params);
        }

        sqlString = "DELETE FROM user_role WHERE USER_ID=?";
        params = new Object[]{user.getUserId()};
        LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
        this.jdbcTemplate.update(sqlString, user.getUserId());

        sqlString = "INSERT INTO user_role (USER_ID, ROLE_ID) VALUES(?, ?)";
        params = new Object[]{user.getUserId(), user.getRole().getRoleId()};
        LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
        this.jdbcTemplate.update(sqlString, params);
    }

    @Override
    public User getUserByUsername(String username) {
        String sqlString = "SELECT user.*, user_role.ROLE_ID, role.ROLE_NAME FROM user LEFT JOIN user_role ON user.USER_ID=user_role.USER_ID LEFT JOIN role ON user_role.ROLE_ID=role.ROLE_ID WHERE user.USERNAME=?";
        Object params[] = new Object[]{username};
        try {
            LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
            return this.jdbcTemplate.queryForObject(sqlString, params, new UserRowMapper());
        } catch (EmptyResultDataAccessException erda) {
            LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog("No User found with username"));
            return null;
        }
    }

    @Override
    public User getUserByUserId(int userId) {
        String sqlString = "SELECT user.*, user_role.ROLE_ID, role.ROLE_NAME FROM user LEFT JOIN user_role ON user.USER_ID=user_role.USER_ID LEFT JOIN role ON user_role.ROLE_ID=role.ROLE_ID WHERE user.USER_ID=?";
        Object params[] = new Object[]{userId};
        try {
            LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
            return this.jdbcTemplate.queryForObject(sqlString, params, new UserRowMapper());
        } catch (EmptyResultDataAccessException erda) {
            LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog("No User found with userId"));
            return null;

        }
    }

    @Override
    public boolean confirmPassword(Password password) {
        String sqlString = "SELECT COUNT(*) FROM user WHERE user.USER_ID=? AND user.PASSWORD=MD5(?)";
        Object params[] = new Object[]{password.getUserId(), password.getOldPassword()};
        LogUtils.systemLogger.info(LogUtils.buildStringForSystemLog(sqlString, params));
        int rowsFound = this.jdbcTemplate.queryForInt(sqlString, params);
        if (rowsFound > 0) {
            return true;
        } else {
            return false;
        }
    }

    @Override
    public void updatePassword(Password password, int offset) {
        Date offsetDate = DateUtils.getOffsetFromCurrentDateObject(DateUtils.IST, offset);
        String sqlString = "UPDATE user SET PASSWORD=MD5(?), EXPIRES_ON=? WHERE user.USER_ID=?";
        Object params[] = new Object[]{password.getNewPassword(), offsetDate, password.getUserId()};
        this.jdbcTemplate.update(sqlString, params);
    }

    @Override
    public List<String> getBusinessFunctionsForUserId(int userId) {
        String sqlString = "SELECT BUSINESS_FUNCTION_ID FROM user_role LEFT JOIN role_business_function ON user_role.ROLE_ID=role_business_function.ROLE_ID WHERE user_role.USER_ID=?";
        return this.jdbcTemplate.queryForList(sqlString, new Object[]{userId}, String.class);
    }

    @Override
    public int incrementFailedCountForUsername(String username) {
        String sqlString = "UPDATE `user` SET user.`FAILED_ATTEMPTS`=user.`FAILED_ATTEMPTS`+1 WHERE user.`USERNAME`=?";
        return this.jdbcTemplate.update(sqlString, username);
    }

    @Override
    public void resetFailedCountForUserId(int userId) {
        String sqlString = "UPDATE `user` SET user.`FAILED_ATTEMPTS`=0 WHERE user.`USER_ID`=?";
        this.jdbcTemplate.update(sqlString, userId);
    }

    @Override
    @Transactional
    public List<User> getUserListForReport() {
        List<User> userList = this.hibernateTemplate.find("from User");
        return userList;
    }

    @Override
    @Transactional
    public List<User> getActiveUserListForTicket() {
        session = this.sessionFactory.getCurrentSession();
        Criteria cr = session.createCriteria(cc.altius.model.User.class);
        cr.add(Restrictions.eq("active", true));
        List<User> users = cr.list();
        return users;
    }
}
